<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');

class Consumption_summary_model extends CI_Model
{
    public function __construct()
    {
        parent::__construct();
    }



    public function get_locations(){
      $sql="SELECT *,aea.zone_1
         , (SELECT(CASE WHEN ar.account_id in (SELECT account_id from ubs_meter_readings where CONCAT(bill_year,'-',bill_month) =  DATE_FORMAT(NOW(),'%Y-%m')) THEN 'YES' ELSE 'NO' END)) as flag
         , umr.consumed
        FROM ar_account ar
          LEFT JOIN (SELECT account_id , consumed FROM ubs_meter_readings WHERE CONCAT(bill_year,'-',bill_month) =  DATE_FORMAT(NOW(),'%Y-%m')) umr on umr.account_id = ar.account_id
          INNER JOIN ar_entity_address aea on ar.acct_addr = aea.address_id
        where  ar.status = 'ACT' 
      ";
      $query = $this->db->query($sql);
      return $query->result();
    }

    public function get_names($zone,$from,$to){
      $sql="SELECT *,aea.zone_1
         , (SELECT(CASE WHEN ar.account_id IN (SELECT account_id FROM ubs_meter_readings WHERE CONCAT(bill_year,'-',bill_month) = DATE_FORMAT(NOW(),'%Y-%m')) THEN 'YES' ELSE 'NO' END)) AS flag
         , umr.consumed
        FROM ar_account ar
          LEFT JOIN (SELECT account_id , consumed FROM ubs_meter_readings WHERE CONCAT(bill_year,'-',bill_month) = DATE_FORMAT(NOW(),'%Y-%m')) umr ON umr.account_id = ar.account_id
          INNER JOIN ar_entity_address aea ON ar.acct_addr = aea.address_id
        where ar.status = 'ACT' and aea.zone_1 = '".$zone."' and umr.consumed BETWEEN '".$from."' AND '".$to."' ";
      $query = $this->db->query($sql);
      return $query->result();
    }

    public function getzoning() {
      $query = $this->db->get('ar_zone1_values_v');
      return $query->result();
    }

    public function getRanges(){
      $sql = "SELECT umr.* , alv.description  , alv.meaning
                  FROM ubs_marker_range umr
                      INNER JOIN app_lookup_values alv on alv.lookup_code = umr.marker_code 
                  ORDER BY umr.range_from ASC";
          $query = $this->db->query($sql);
          return $query->result();
    }



   public function get_arrears(){
    $sql="SELECT tbl.area,
        COUNT(if(tbl.a_count='2',1,NULL)) as c2,
        COUNT(if(tbl.a_count='3',1,NULL)) as c3,
        COUNT(if(tbl.a_count='4',1,NULL)) as c4,
        COUNT(if(tbl.a_count>4,1,NULL)) as above4
    FROM (
                    SELECT aea.zone_1 area
                      ,aa.acct_num num
                      ,aa.acct_name name
                        ,count(x.customer_id) a_count
                    FROM ar_account aa
                    INNER JOIN ar_entity_address aea
                          ON aa.acct_addr = aea.address_id
                    LEFT JOIN (SELECT customer_id FROM ubs_invoices_payments_v WHERE ifnull(sum_pay,0) != ifnull(invoice_total,0)) x ON x.customer_id = aa.account_id
                    WHERE aa.enable_flag = 'Y' AND aa.status ='ACT'
                    AND LAST_DAY(aa.active_start)
                      BETWEEN LAST_DAY(aa.active_start)
                      AND IFNULL(LAST_DAY(aa.active_end), LAST_DAY(NOW()))
                      GROUP BY aea.zone_1,aa.acct_num,aa.acct_name 
                  ) tbl
                  WHERE a_count > 1
                  GROUP BY tbl.area
                  ORDER BY tbl.a_count ASC";

    $query = $this->db->query($sql);
    return $query->result();
   

   }

   public function get_summary($yr,$mn){
      $sql= "SELECT aea.zone_1,
      ur.range_from rf ,
      ur.range_to rt,
       count(*) as cc
      FROM ubs_meter_readings umr 
        INNER JOIN ar_account aa ON aa.account_id = umr.account_id
          INNER JOIN ar_entity_address aea ON aea.address_id = aa.acct_addr
          INNER JOIN ubs_marker_range ur ON umr.consumed BETWEEN ur.range_from and ur.range_to
         WHERE umr.bill_month = ".$mn."  AND ".$yr."  AND aa.status = 'ACT'

      GROUP BY ur.range_from,aea.zone_1";
      $query = $this->db->query($sql);
      return $query->result();
   }


  //  public function get_names($id,$zone){
  //   $where = "";
  //   if($id == 5){
  //     $where = " WHERE a_count > 4 AND area = '".$zone."' ";
  //   }else{
  //     $where = " WHERE a_count = ".$id." AND area = '".$zone."' ";
  //   }


  //   $q = $this->db->query("SELECT tbl.area,tbl.name,tbl.num,tbl.a_count FROM (

  //                   SELECT aea.zone_1 area

  //                     ,aa.acct_num num

  //                     ,aa.acct_name name

  //                       ,count(x.customer_id) a_count

  //                   FROM ar_account aa

  //                   INNER JOIN ar_entity_address aea

  //                         ON aa.acct_addr = aea.address_id

  //                   LEFT JOIN (SELECT customer_id FROM ubs_invoices_payments_v WHERE (ifnull(sum_pay,0) != ifnull(invoice_total,0) AND ifnull(sum_pay,0) <= ifnull(invoice_total,0))) x ON x.customer_id = aa.account_id

  //                   WHERE aa.enable_flag = 'Y' and aa.status = 'ACT'

  //                   AND LAST_DAY(aa.active_start)

  //                     BETWEEN LAST_DAY(aa.active_start)

  //                     AND IFNULL(LAST_DAY(aa.active_end), LAST_DAY(NOW()))

  //                     GROUP BY aea.zone_1,aa.acct_num,aa.acct_name 

  //                 ) tbl

  //                 ".$where." 

  //                 GROUP BY num ");

  //   return $q->result();

  // }


  // public function get_pnd($id,$zone){
  //   $where = "";
  //   if($id == 5){
  //     $where = " WHERE a_count > 4 AND area = '".$zone."' ";
  //   }else{
  //     $where = " WHERE a_count = ".$id." AND area = '".$zone."' ";
  //   }


  //   $q = $this->db->query("SELECT aea.zone_1 AREA, 
  //                                 aa.acct_num num, 
  //                                 aa.acct_name NAME, 
  //                                 uipv.invoice_num inv_num, 
  //                                 uipv.invoice_date inv_date, 
  //                                 (IFNULL(uipv.invoice_total,0) - IFNULL(uipv.sum_pay,0)) balance,
  //                                 uipv.due_date due_date
  //                          FROM ubs_invoices_payments_v uipv

  //               INNER JOIN ar_account aa ON aa.account_id = uipv.customer_id

  //                 INNER JOIN ar_entity_address aea ON aea.address_id = aa.acct_addr 

  //                 WHERE (IFNULL(sum_pay,0) != IFNULL(invoice_total,0) AND ifnull(sum_pay,0) <= ifnull(invoice_total,0))

  //                 AND aa.acct_num IN (SELECT  tbl.num FROM (

  //                   SELECT aea.zone_1 area

  //                     ,aa.acct_num num

  //                     ,aa.acct_name name

  //                       ,count(x.customer_id) a_count

  //                   FROM ar_account aa

  //                   INNER JOIN ar_entity_address aea

  //                         ON aa.acct_addr = aea.address_id

  //                   LEFT JOIN (SELECT customer_id FROM ubs_invoices_payments_v WHERE (ifnull(sum_pay,0) != ifnull(invoice_total,0) AND ifnull(sum_pay,0) <= ifnull(invoice_total,0))) x ON x.customer_id = aa.account_id

  //                   WHERE aa.enable_flag = 'Y' and aa.status = 'ACT'

  //                   AND LAST_DAY(aa.active_start)

  //                     BETWEEN LAST_DAY(aa.active_start)

  //                     AND IFNULL(LAST_DAY(aa.active_end), LAST_DAY(NOW()))

  //                     GROUP BY aea.zone_1,aa.acct_num,aa.acct_name 

  //                 ) tbl

  //                 ".$where." 

  //                 GROUP BY num)

  //                 ORDER BY uipv.invoice_date DESC");

  //   return $q->result();

  // }

   
}